<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>FK Lookup table using an integer-based Foreign Key - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span></li>
<li class="tocentry"><a href="SingleModelCrud.htm">Operations on a single entity type</a>
</li>
<li class="tocentry"><a href="MultipleCrud.htm">Operations on sets</a>
</li>
<li class="tocentry"><a href="ModelWithChildren.htm">Operations on a graph of multiple entity types</a>
</li>
<li class="tocentry"><a href="Immutable.htm">Operations on immutable entities</a>
</li>
<li class="tocentry"><a href="TryCrud.htm">Handling failures and exceptions</a>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="OperationswithaForeignKeybasedLookupTable.htm">Operations with a Foreign Key based Lookup Table</a></span></li>
<li class="tocentry current"><a class="current" href="ModelWithLookupSimple.htm">FK Lookup table using an integer-based Foreign Key</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>
<li class="tocentry"><a href="ModelWithLookupComplex.htm">FK Lookup table using an object-based Foreign Key</a>
</li>

</ul>
</li>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></li> / <li><a href="OperationswithaForeignKeybasedLookupTable.htm">Operations with a Foreign Key based Lookup Table</a></li> / <li><a href="ModelWithLookupSimple.htm">FK Lookup table using an integer-based Foreign Key</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="crud-operations-on-model-with-integer-based-foreign-key">CRUD Operations on Model with Integer-Based Foreign Key<a class="headerlink" href="#crud-operations-on-model-with-integer-based-foreign-key" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>This scenario demonstrates performing Create, Read, Update, and Delete operations on an object that has a foreign key reference to a lookup table. The FK reference is represented as an integer.</p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IEmployeeSimple
{
    string? CellPhone { get; set; }
    int EmployeeClassificationKey { get; set; }
    int EmployeeKey { get; set; }
    string? FirstName { get; set; }
    string? LastName { get; set; }
    string? MiddleName { get; set; }
    string? OfficePhone { get; set; }
    string? Title { get; set; }
}
</code></pre>

<pre><code class="cs">public interface IModelWithLookupSimpleScenario&lt;TEmployee&gt;
   where TEmployee : class, IEmployeeSimple, new()
{
    /// &lt;summary&gt;
    /// Create a new Employee row, returning the new primary key.
    /// &lt;/summary&gt;
    int Create(TEmployee employee);

    /// &lt;summary&gt;
    /// Delete a Employee row using an object.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void Delete(TEmployee employee);

    /// &lt;summary&gt;
    /// Delete a Employee row using its primary key.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void DeleteByKey(int employeeKey);

    /// &lt;summary&gt;
    /// Gets an Employee row by its name.
    /// &lt;/summary&gt;
    IList&lt;TEmployee&gt; FindByLastName(string lastName);

    /// &lt;summary&gt;
    /// Gets all Employee rows.
    /// &lt;/summary&gt;
    IList&lt;TEmployee&gt; GetAll();

    /// &lt;summary&gt;
    /// Gets an Employee row by its primary key.
    /// &lt;/summary&gt;
    TEmployee? GetByKey(int employeeKey);

    /// &lt;summary&gt;
    /// Get an employee classification by key.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employeeClassificationKey&quot;&gt;The employee classification key.&lt;/param&gt;
    IEmployeeClassification? GetClassification(int employeeClassificationKey);

    /// &lt;summary&gt;
    /// Update a Employee row.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void Update(TEmployee employee);
}
</code></pre>

<h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>In order to promote code reuse, object population has been moved into the model's constructor.</p>
<pre><code class="cs">public class EmployeeSimple : IEmployeeSimple
{
    public EmployeeSimple()
    {
    }

    public EmployeeSimple(IDataReader reader)
    {
        Refresh(reader);
    }

    public string? CellPhone { get; set; }

    public int EmployeeClassificationKey { get; set; }

    public int EmployeeKey { get; set; }

    public string? FirstName { get; set; }

    public string? LastName { get; set; }

    public string? MiddleName { get; set; }

    public string? OfficePhone { get; set; }

    public string? Title { get; set; }

    /// &lt;summary&gt;
    /// Refreshes the object, replacing all fields with values from the IDataReader.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;reader&quot;&gt;The reader.&lt;/param&gt;
    /// &lt;exception cref=&quot;System.ArgumentNullException&quot;&gt;reader&lt;/exception&gt;
    public void Refresh(IDataReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException(nameof(reader), $&quot;{nameof(reader)} is null.&quot;);

        EmployeeKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeKey&quot;));
        FirstName = reader.GetString(reader.GetOrdinal(&quot;FirstName&quot;));
        if (!reader.IsDBNull(reader.GetOrdinal(&quot;MiddleName&quot;)))
            MiddleName = reader.GetString(reader.GetOrdinal(&quot;MiddleName&quot;));
        LastName = reader.GetString(reader.GetOrdinal(&quot;LastName&quot;));
        if (!reader.IsDBNull(reader.GetOrdinal(&quot;Title&quot;)))
            Title = reader.GetString(reader.GetOrdinal(&quot;Title&quot;));
        if (!reader.IsDBNull(reader.GetOrdinal(&quot;OfficePhone&quot;)))
            OfficePhone = reader.GetString(reader.GetOrdinal(&quot;OfficePhone&quot;));
        if (!reader.IsDBNull(reader.GetOrdinal(&quot;CellPhone&quot;)))
            CellPhone = reader.GetString(reader.GetOrdinal(&quot;CellPhone&quot;));
        EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;));
    }
}
</code></pre>

<pre><code class="cs">    public class ModelWithLookupSimpleScenario : SqlServerScenarioBase, IModelWithLookupSimpleScenario&lt;EmployeeSimple&gt;
    {
        public ModelWithLookupSimpleScenario(string connectionString) : base(connectionString)
        { }

        public int Create(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

            const string sql = @&quot;INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey
VALUES
(@FirstName, @MiddleName, @LastName, @Title, @OfficePhone, @CellPhone, @EmployeeClassificationKey);&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@FirstName&quot;, employee.FirstName);
                cmd.Parameters.AddWithValue(&quot;@MiddleName&quot;, (object?)employee.MiddleName ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@LastName&quot;, employee.LastName);
                cmd.Parameters.AddWithValue(&quot;@Title&quot;, (object?)employee.Title ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@OfficePhone&quot;, (object?)employee.OfficePhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@CellPhone&quot;, (object?)employee.CellPhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employee.EmployeeClassificationKey);

                return (int)cmd.ExecuteScalar();
            }
        }

        public void Delete(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

            const string sql = @&quot;DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@EmployeeKey&quot;, employee.EmployeeKey);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteByKey(int employeeKey)
        {
            const string sql = @&quot;DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@EmployeeKey&quot;, employeeKey);
                cmd.ExecuteNonQuery();
            }
        }

        public IList&lt;EmployeeSimple&gt; FindByLastName(string lastName)
        {
            const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName&quot;;

            var result = new List&lt;EmployeeSimple&gt;();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@LastName&quot;, lastName);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result.Add(new EmployeeSimple(reader));
                    }
                    return result;
                }
            }
        }

        public IList&lt;EmployeeSimple&gt; GetAll()
        {
            const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e&quot;;

            var result = new List&lt;EmployeeSimple&gt;();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    result.Add(new EmployeeSimple(reader));
                }
                return result;
            }
        }

        public EmployeeSimple? GetByKey(int employeeKey)
        {
            const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e WHERE e.EmployeeKey = @EmployeeKey&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@EmployeeKey&quot;, employeeKey);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                        return null;

                    return new EmployeeSimple(reader);
                }
            }
        }

        public IEmployeeClassification? GetClassification(int employeeClassificationKey)
        {
            const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee
                        FROM HR.EmployeeClassification ec
                        WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                        return null;

                    return new EmployeeClassification()
                    {
                        EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                        EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;)),
                        IsExempt = reader.GetBoolean(reader.GetOrdinal(&quot;IsExempt&quot;)),
                        IsEmployee = reader.GetBoolean(reader.GetOrdinal(&quot;IsEmployee&quot;))
                    };
                }
            }
        }

        public void Update(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

            const string sql = @&quot;UPDATE HR.Employee
SET FirstName = @FirstName,
    MiddleName = @MiddleName,
    LastName = @LastName,
    Title = @Title,
    OfficePhone = @OfficePhone,
    CellPhone = @CellPhone,
    EmployeeClassificationKey = @EmployeeClassificationKey
WHERE EmployeeKey = @EmployeeKey;&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@EmployeeKey&quot;, employee.EmployeeKey);

                cmd.Parameters.AddWithValue(&quot;@FirstName&quot;, employee.FirstName);
                cmd.Parameters.AddWithValue(&quot;@MiddleName&quot;, (object?)employee.MiddleName ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@LastName&quot;, employee.LastName);
                cmd.Parameters.AddWithValue(&quot;@Title&quot;, (object?)employee.Title ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@OfficePhone&quot;, (object?)employee.OfficePhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@CellPhone&quot;, (object?)employee.CellPhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employee.EmployeeClassificationKey);

                cmd.ExecuteNonQuery();
            }
        }
    }
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario&lt;EmployeeSimple&gt;
{
    readonly SqlServerDataSource m_DataSource;

    public ModelWithLookupSimpleScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int Create(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        return m_DataSource.Insert(employee).ToInt32().Execute();
    }

    public void Delete(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        m_DataSource.Delete(employee).Execute();
    }

    public void DeleteByKey(int employeeKey)
    {
        m_DataSource.DeleteByKey&lt;EmployeeSimple&gt;(employeeKey).Execute();
    }

    public IList&lt;EmployeeSimple&gt; FindByLastName(string lastName)
    {
        throw new NotImplementedException();
    }

    public IList&lt;EmployeeSimple&gt; GetAll()
    {
        return m_DataSource.From&lt;EmployeeSimple&gt;().ToCollection().Execute();
    }

    public EmployeeSimple? GetByKey(int employeeKey)
    {
        return m_DataSource.GetByKey&lt;EmployeeSimple&gt;(employeeKey).ToObjectOrNull().Execute();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        return m_DataSource.GetByKey&lt;EmployeeClassification&gt;(employeeClassificationKey).ToObject().Execute();
    }

    public void Update(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        m_DataSource.Update(employee).Execute();
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">    public class ModelWithLookupSimpleScenario : ScenarioBase, IModelWithLookupSimpleScenario&lt;EmployeeSimple&gt;
    {
        public ModelWithLookupSimpleScenario(string connectionString) : base(connectionString)
        {
        }

        public int Create(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

            const string sql = @&quot;INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey
VALUES
(@FirstName, @MiddleName, @LastName, @Title, @OfficePhone, @CellPhone, @EmployeeClassificationKey);&quot;;

            using (var con = OpenConnection())
                return (int)con.ExecuteScalar(sql, employee);
        }

        public void Delete(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

            const string sql = @&quot;DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;&quot;;

            using (var con = OpenConnection())
                con.Execute(sql, employee);
        }

        public void DeleteByKey(int employeeKey)
        {
            const string sql = @&quot;DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;&quot;;

            using (var con = OpenConnection())
                con.Execute(sql, new { employeeKey });
        }

        public IList&lt;EmployeeSimple&gt; FindByLastName(string lastName)
        {
            const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName&quot;;

            var result = new List&lt;EmployeeSimple&gt;();

            using (var con = OpenConnection())
                return con.Query&lt;EmployeeSimple&gt;(sql, new { lastName }).ToList();
        }

        public IList&lt;EmployeeSimple&gt; GetAll()
        {
            const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e&quot;;

            var result = new List&lt;EmployeeSimple&gt;();

            using (var con = OpenConnection())
                return con.Query&lt;EmployeeSimple&gt;(sql).ToList();
        }

        public EmployeeSimple? GetByKey(int employeeKey)
        {
            const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e WHERE e.EmployeeKey = @EmployeeKey&quot;;

            using (var con = OpenConnection())
                return con.QuerySingleOrDefault&lt;EmployeeSimple&gt;(sql, new { employeeKey });
        }

        public IEmployeeClassification? GetClassification(int employeeClassificationKey)
        {
            const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee
                        FROM HR.EmployeeClassification ec
                        WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

            using (var con = OpenConnection())
                return con.QuerySingle&lt;EmployeeClassification&gt;(sql, new { EmployeeClassificationKey = employeeClassificationKey });
        }

        public void Update(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

            const string sql = @&quot;UPDATE HR.Employee
SET FirstName = @FirstName,
    MiddleName = @MiddleName,
    LastName = @LastName,
    Title = @Title,
    OfficePhone = @OfficePhone,
    CellPhone = @CellPhone,
    EmployeeClassificationKey = @EmployeeClassificationKey
WHERE EmployeeKey = @EmployeeKey;&quot;;

            using (var con = OpenConnection())
                con.Execute(sql, employee);
        }
    }
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : ScenarioBase, IModelWithLookupSimpleScenario&lt;EmployeeSimple&gt;
{
    public ModelWithLookupSimpleScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        const string sql = @&quot;INSERT INTO HR.Employee
            (FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
            OUTPUT Inserted.EmployeeKey
            VALUES
            (@FirstName, @MiddleName, @LastName, @Title, @OfficePhone, @CellPhone, @EmployeeClassificationKey);&quot;;


        return DbConnector.Scalar&lt;int&gt;(sql, employee).Execute();
    }

    public void Delete(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        const string sql = @&quot;DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;&quot;;


        DbConnector.NonQuery(sql, employee).Execute();
    }

    public void DeleteByKey(int employeeKey)
    {
        const string sql = @&quot;DELETE HR.Employee WHERE EmployeeKey = @employeeKey;&quot;;


        DbConnector.NonQuery(sql, new { employeeKey }).Execute();
    }

    public IList&lt;EmployeeSimple&gt; FindByLastName(string lastName)
    {
        const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @lastName&quot;;

        return DbConnector.ReadToList&lt;EmployeeSimple&gt;(sql, new { lastName }).Execute();
    }

    public IList&lt;EmployeeSimple&gt; GetAll()
    {
        const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e&quot;;

        return DbConnector.ReadToList&lt;EmployeeSimple&gt;(sql).Execute();
    }

    public EmployeeSimple? GetByKey(int employeeKey)
    {
        const string sql = @&quot;SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.EmployeeKey = @employeeKey&quot;;

        return DbConnector.ReadSingleOrDefault&lt;EmployeeSimple&gt;(sql, new { employeeKey }).Execute();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;&quot;;

        return DbConnector.ReadSingle&lt;EmployeeClassification&gt;(sql, new { employeeClassificationKey }).Execute();
    }

    public void Update(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        const string sql = @&quot;UPDATE HR.Employee
        SET FirstName = @FirstName,
            MiddleName = @MiddleName,
            LastName = @LastName,
            Title = @Title,
            OfficePhone = @OfficePhone,
            CellPhone = @CellPhone,
            EmployeeClassificationKey = @EmployeeClassificationKey
        WHERE EmployeeKey = @EmployeeKey;&quot;;

        DbConnector.NonQuery(sql, employee).Execute();
    }
}
</code></pre>

<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario&lt;Employee&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public ModelWithLookupSimpleScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Employee.Add(employee);
            context.SaveChanges();
            return employee.EmployeeKey;
        }
    }

    public void Delete(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(employee).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public void DeleteByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.Employee.Find(employeeKey);
            if (temp != null)
            {
                context.Employee.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public IList&lt;Employee&gt; FindByLastName(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employee.Where(ec =&gt; ec.LastName == lastName).ToList();
    }

    public IList&lt;Employee&gt; GetAll()
    {
        using (var context = CreateDbContext())
            return context.Employee.ToList();
    }

    public Employee? GetByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employee.Find(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassification.Find(employeeClassificationKey);
    }

    /// &lt;summary&gt;
    /// Updates the specified employee.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employee&quot;&gt;The employee.&lt;/param&gt;
    /// &lt;exception cref=&quot;ArgumentNullException&quot;&gt;employee&lt;/exception&gt;
    public void Update(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(employee).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}
</code></pre>

<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario&lt;Employee&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public ModelWithLookupSimpleScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Employees.Add(employee);
            context.SaveChanges();
            return employee.EmployeeKey;
        }
    }

    public void Delete(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(employee).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public void DeleteByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.Employees.Find(employeeKey);
            if (temp != null)
            {
                context.Employees.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public IList&lt;Employee&gt; FindByLastName(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employees.Where(ec =&gt; ec.LastName == lastName).ToList();
    }

    public IList&lt;Employee&gt; GetAll()
    {
        using (var context = CreateDbContext())
            return context.Employees.ToList();
    }

    public Employee? GetByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employees.Find(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassifications.Find(employeeClassificationKey);
    }

    /// &lt;summary&gt;
    /// Updates the specified employee.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employee&quot;&gt;The employee.&lt;/param&gt;
    /// &lt;exception cref=&quot;ArgumentNullException&quot;&gt;employee&lt;/exception&gt;
    public void Update(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(employee).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario&lt;EmployeeEntity&gt;
{
    public int Create(EmployeeEntity employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(employee, true, recurse: false);
            return employee.EmployeeKey;
        }
    }

    public void Delete(EmployeeEntity employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var adapter = new DataAccessAdapter())
        {
            employee.IsNew = false;
            adapter.DeleteEntity(employee);
        }
    }

    public void DeleteByKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            adapter.DeleteEntitiesDirectly(typeof(EmployeeEntity), new RelationPredicateBucket(EmployeeFields.EmployeeKey.Equal(employeeKey)));
        }
    }

    public IList&lt;EmployeeEntity&gt; FindByLastName(string lastName)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Employee.Where(ec =&gt; ec.LastName == lastName).ToList();
        }
    }

    public IList&lt;EmployeeEntity&gt; GetAll()
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Employee.ToList();
        }
    }

    public EmployeeEntity? GetByKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return adapter.FetchFirst(new QueryFactory().Employee.Where(EmployeeFields.EmployeeKey.Equal(employeeKey)));
        }
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeClassification.FirstOrDefault(ec =&gt; ec.EmployeeClassificationKey == employeeClassificationKey);
        }
    }

    /// &lt;summary&gt;
    /// Updates the specified employee.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employee&quot;&gt;The employee.&lt;/param&gt;
    /// &lt;exception cref=&quot;ArgumentNullException&quot;&gt;employee&lt;/exception&gt;
    public void Update(EmployeeEntity employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        using (var adapter = new DataAccessAdapter())
        {
            EmployeeEntity toPersist = employee;
            if (toPersist.IsNew)
            {
                toPersist = new EmployeeEntity(employee.EmployeeKey);
                adapter.FetchEntity(toPersist);
                //Copy the changed fields
                toPersist.FirstName = employee.FirstName;
                toPersist.MiddleName = employee.MiddleName;
                toPersist.LastName = employee.LastName;
                toPersist.CellPhone = employee.CellPhone;
                toPersist.OfficePhone = employee.OfficePhone;
                toPersist.Title = employee.Title;
                toPersist.EmployeeClassificationKey = employee.EmployeeClassificationKey;
            }
            if (!toPersist.IsNew)
            {
                adapter.SaveEntity(toPersist, refetchAfterSave: false, recurse: false);
            }
        }
    }
}
</code></pre>

<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : DbRepository&lt;SqlConnection&gt;,
    IModelWithLookupSimpleScenario&lt;EmployeeSimple&gt;
{
    public ModelWithLookupSimpleScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int Create(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        return Insert&lt;EmployeeSimple, int&gt;(employee);
    }

    public void Delete(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        base.Delete(employee);
    }

    public void DeleteByKey(int employeeKey)
    {
        Delete&lt;EmployeeSimple&gt;(employeeKey);
    }

    public IList&lt;EmployeeSimple&gt; FindByLastName(string lastName)
    {
        return Query&lt;EmployeeSimple&gt;(e =&gt; e.LastName == lastName).AsList();
    }

    public IList&lt;EmployeeSimple&gt; GetAll()
    {
        return QueryAll&lt;EmployeeSimple&gt;().AsList();
    }

    public EmployeeSimple? GetByKey(int employeeKey)
    {
        return Query&lt;EmployeeSimple&gt;(employeeKey).FirstOrDefault();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        return Query&lt;EmployeeClassification&gt;(employeeClassificationKey).FirstOrDefault();
    }

    public void Update(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);

        base.Update(employee);
    }
}
</code></pre>

<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario&lt;Employee&gt;
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    public ModelWithLookupSimpleScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return (int) db.Insert(employee, true);
    }

    public void Delete(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $&quot;{nameof(employee)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Delete(employee);
    }

    public void DeleteByKey(int employeeKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.DeleteById&lt;Employee&gt;(employeeKey);
    }

    public IList&lt;Employee&gt; FindByLastName(string lastName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Select&lt;Employee&gt;(e =&gt; e.LastName == lastName);
    }

    public IList&lt;Employee&gt; GetAll()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Select&lt;Employee&gt;();
    }

    public Employee? GetByKey(int employeeKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.SingleById&lt;Employee&gt;(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.SingleById&lt;EmployeeClassification&gt;(employeeClassificationKey);
    }

    public void Update(Employee employee)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(employee);
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
